package alvarodelrosal.chat.server.infraestructura;

import alvarodelrosal.chat.server.modelo.ChatConversation;
import alvarodelrosal.chat.server.modelo.ChatMessage;
import alvarodelrosal.chat.server.modelo.ChatUser;
import java.io.BufferedReader;
import java.io.File;
import java.io.FileReader;
import java.io.IOException;
import java.sql.*;
import java.util.ArrayList;
import java.util.List;
import java.util.Set;
import java.util.logging.Level;
import java.util.logging.Logger;

public class ChatsDBPersistence implements ChatsPersistence {

    private Connection dbConnection = null;
    private static ChatsPersistence persistence = null;
    private static Object lock = new Object();

    private ChatsDBPersistence() {
        BufferedReader reader = null;
        try {
            String file = "config.txt";
            reader = new BufferedReader(new FileReader(new File(file)));
            String url = reader.readLine();
            String username = reader.readLine();
            String password = reader.readLine();
            try {
                Class.forName("org.apache.derby.jdbc.ClientDriver");
                dbConnection = DriverManager.getConnection(url, username, password);
            } catch (ClassNotFoundException ex) {
                Logger.getLogger(ChatUsersDBPersistence.class.getName()).log(Level.SEVERE, null, ex);
            } catch (SQLException ex) {
                Logger.getLogger(ChatUsersDBPersistence.class.getName()).log(Level.SEVERE, null, ex);
            }
        } catch (IOException ex) {
            Logger.getLogger(ChatUsersDBPersistence.class.getName()).log(Level.SEVERE, null, ex);
        } finally {
            if (reader != null) {
                try {
                    reader.close();
                } catch (IOException ex) {
                    Logger.getLogger(ChatUsersDBPersistence.class.getName()).log(Level.SEVERE, null, ex);
                }
            }
        }
    }

    public static ChatsPersistence getPersistence() {
        if (persistence == null) {
            persistence = new ChatsDBPersistence();
        }
        return persistence;
    }

    @Override
    public List<Integer> getAllChats() {
        List<Integer> chats = new ArrayList();
        PreparedStatement statement = null;
        ResultSet result = null;
        try {
            synchronized (lock) {
                statement = dbConnection.prepareStatement(""
                        + "SELECT chat_id"
                        + "  FROM chat");
            }
            result = statement.executeQuery();
            while (result.next()) {
                chats.add(result.getInt("chat_id"));
            }

            return chats;

        } catch (SQLException ex) {
            Logger.getLogger(ChatUsersDBPersistence.class.getName()).log(Level.SEVERE, null, ex);
        } finally {
            if (statement != null) {
                try {
                    statement.close();
                } catch (SQLException ex) {
                    Logger.getLogger(ChatUsersDBPersistence.class.getName()).log(Level.SEVERE, null, ex);
                } finally {
                    if (result != null) {
                        try {
                            result.close();
                        } catch (SQLException ex) {
                            Logger.getLogger(ChatsDBPersistence.class.getName()).log(Level.SEVERE, null, ex);
                        }
                    }
                }
            }
        }
        return chats;
    }

    @Override
    public String getChatName(int ChatId) {
        PreparedStatement statement = null;
        ResultSet result = null;
        try {
            synchronized (lock) {
                statement = dbConnection.prepareStatement(""
                        + "SELECT chat_subject"
                        + "  FROM chat");
            }
            result = statement.executeQuery();
            result.next();
            return result.getString("chat_subject");

        } catch (SQLException ex) {
            Logger.getLogger(ChatUsersDBPersistence.class.getName()).log(Level.SEVERE, null, ex);
        } finally {
            if (statement != null) {
                try {
                    statement.close();
                } catch (SQLException ex) {
                    Logger.getLogger(ChatUsersDBPersistence.class.getName()).log(Level.SEVERE, null, ex);
                } finally {
                    if (result != null) {
                        try {
                            result.close();
                        } catch (SQLException ex) {
                            Logger.getLogger(ChatsDBPersistence.class.getName()).log(Level.SEVERE, null, ex);
                        }
                    }
                }
            }
        }
        return "";
    }

    @Override
    public List<ChatMessage> getMessagesFrom(int chatId) {
        List<ChatMessage> messages = new ArrayList();

        ResultSet result = null;
        PreparedStatement statement = null;
        try {
            synchronized (lock) {
                statement = dbConnection.prepareStatement(""
                        + "SELECT user_id, message_text"
                        + "  FROM message"
                        + " WHERE chat_id = ?");
            }
            statement.setInt(1, chatId);

            result = statement.executeQuery();

            while (result.next()) {
                ChatUser user = ChatPersistenceManager.getPersistenceManager().
                        getTheUser(result.getInt("user_id"));
                messages.add(new ChatMessage(user, result.getString("message_text")));
            }

            return messages;

        } catch (SQLException ex) {
            Logger.getLogger(ChatUsersDBPersistence.class.getName()).log(Level.SEVERE, null, ex);
        } finally {
            if (statement != null) {
                try {
                    statement.close();
                } catch (SQLException ex) {
                    Logger.getLogger(ChatUsersDBPersistence.class.getName()).log(Level.SEVERE, null, ex);
                } finally {
                    if (result != null) {
                        try {
                            result.close();
                        } catch (SQLException ex) {
                            Logger.getLogger(ChatsDBPersistence.class.getName()).log(Level.SEVERE, null, ex);
                        }
                    }
                }
            }
        }
        return messages;
    }

    @Override
    public List<Integer> getChatsWith(int userId) {
        List<Integer> chats = new ArrayList();
        PreparedStatement statement = null;
        ResultSet result = null;
        try {
            synchronized (lock) {
                statement = dbConnection.prepareStatement(""
                        + "SELECT chat_id"
                        + "  FROM conversation"
                        + " WHERE user_id = ?");
            }
            statement.setInt(1, userId);

            result = statement.executeQuery();

            while (result.next()) {
                chats.add(result.getInt("chat_id"));
            }

            return chats;

        } catch (SQLException ex) {
            Logger.getLogger(ChatUsersDBPersistence.class.getName()).log(Level.SEVERE, null, ex);
        } finally {
            if (statement != null) {
                try {
                    statement.close();
                } catch (SQLException ex) {
                    Logger.getLogger(ChatUsersDBPersistence.class.getName()).log(Level.SEVERE, null, ex);
                } finally {
                    if (result != null) {
                        try {
                            result.close();
                        } catch (SQLException ex) {
                            Logger.getLogger(ChatsDBPersistence.class.getName()).log(Level.SEVERE, null, ex);
                        }
                    }
                }
            }
        }
        return chats;
    }

    @Override
    public void save(ChatConversation conversation) {

        PreparedStatement statement = null;
        ResultSet result = null;
        try {
            synchronized (lock) {
                statement = dbConnection.prepareStatement(""
                        + "SELECT chat_id"
                        + "  FROM chat "
                        + "ORDER BY chat_id DESC");
            }
            result = statement.executeQuery();

            int chatId = 0;
            if (result.next()) {
                chatId = result.getInt("chat_id");
            }
            chatId++;

            synchronized (lock) {
                statement = dbConnection.prepareStatement("INSERT INTO chat "
                        + "(chat_id, chat_subject) "
                        + "VALUES (?, ?)");
            }
            statement.setInt(1, chatId);
            statement.setString(2, conversation.getName());
            statement.execute();

            synchronized (lock) {
                statement = dbConnection.prepareStatement("INSERT INTO conversation "
                        + "(chat_id, user_id) "
                        + "VALUES (?, ?)");
            }
            Set<ChatUser> users = conversation.getHistoricUsers();

            for (ChatUser user : users) {
                statement.setInt(1, chatId);
                statement.setInt(2, user.getUserId());
                statement.execute();
            }

            synchronized (lock) {
                statement = dbConnection.prepareStatement(""
                        + "SELECT message_id"
                        + "  FROM message "
                        + "ORDER BY message_id DESC");

                result = statement.executeQuery();

                int messageId = 0;
                if (result.next()) {
                    messageId = result.getInt("message_id");
                }
                messageId++;
                synchronized (lock) {
                    statement = dbConnection.prepareStatement("INSERT INTO message "
                            + "(message_id, chat_id, user_id, message_text) "
                            + "VALUES (?, ?, ?, ?)");
                }
                List<ChatMessage> messages = conversation.getMessages();

                for (ChatMessage message : messages) {
                    statement.setInt(1, messageId);
                    statement.setInt(2, chatId);
                    statement.setInt(3, message.getUser().getUserId());
                    statement.setString(4, message.getText());
                    statement.execute();
                }
            }


        } catch (SQLException ex) {
            Logger.getLogger(ChatUsersDBPersistence.class.getName()).log(Level.SEVERE, null, ex);
        } finally {
            if (statement != null) {
                try {
                    statement.close();
                } catch (SQLException ex) {
                    Logger.getLogger(ChatUsersDBPersistence.class.getName()).log(Level.SEVERE, null, ex);
                } finally {
                    if (result != null) {
                        try {
                            result.close();
                        } catch (SQLException ex) {
                            Logger.getLogger(ChatsDBPersistence.class.getName()).log(Level.SEVERE, null, ex);
                        }
                    }
                }
            }
        }

    }
}
